package com.eascs.loansettled.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.admin.o2o.internal.util.StatusContexts;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.bankloan.dao.O2oBankLoanDao;
import com.eascs.bankloan.entity.O2oBankLoanEntity;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.loansettled.entity.O2oLoanSettledFormEntity;

@Service
public class O2oLoanSettledService {

	@Autowired
	O2oBankLoanDao loanapplyDao;

	/**
	 * 分页查询申报中的贷款数据
	 * 
	 * @return
	 */
	public PageResult<Object[]> findByPage(O2oLoanSettledFormEntity entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 100, entity.getO2oIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append("  SELECT oll.loanapply_no,oll.bill_code,olu.user_full_name,loan.loan_bank,CONCAT(oll.ltd_code,'-',oll.ltd_name)  as ltd_name,oll.loan_type,opr.province_name,");
		querySql.append(" CAST(ROUND((CASE WHEN(CASE WHEN (oll.bank_amount ) IS NULL THEN oll.ea_mount ELSE(oll.bank_amount)END) IS NULL THEN ");
		querySql.append(" oll.loana_mount ELSE oll.ea_mount END),2)as DECIMAL(10,2))/10000 as loan_amount,DATE_FORMAT(loan.loan_date,'%Y-%m-%d') as loan_date,");
		querySql.append(" DATE_FORMAT(oll.end_bank_date,'%Y-%m-%d'),oll.wf_type");
		querySql.append("  from o2o_loanapply oll LEFT JOIN o2o_loan_user olu on oll.loanapply_no = olu.loanapply_no and oll.loan_id = olu.loan_id");
		querySql.append("  left join  o2o_bank_loan_data loan on oll.bill_code =loan.bill_code ");
		querySql.append("  LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
		querySql.append("  where /**DATA_PERMISSION(oll.ltd)**/ oll.loan_flg='Y' and oll.wf_type !='HN'  and olu.sponsor_type is null and olu.spouse_id is null "); 
		
		if (!StringUtil.isEmpty(entity.getBillCode())) {
			querySql.append(" AND oll.BILL_CODE LIKE ?");
			sqlPageQuery.addLikeParam(entity.getBillCode());
		}
		if (!StringUtil.isEmpty(entity.getBankLoan())) {
			querySql.append(" AND loan.loan_bank = ?");
			sqlPageQuery.addParam(entity.getBankLoan());
		}
		if (StringUtil.hasText(entity.getLoanType())) {
			querySql.append(" and oll.loan_type in (");
			String loanTypeA[] = {};
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if (entity.getLoanType().length() > 4) {
				loanTypeA = entity.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			} else {
				loanTypes.append("'" + entity.getLoanType() + "',");
			}
			loanType = loanTypes.toString().substring(0, loanTypes.toString().length() - 1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if (!StringUtil.isEmpty(entity.getOperName())) {
			querySql.append(" AND oll.OPERATOR_NAME LIKE ?");
			sqlPageQuery.addLikeParam(entity.getOperName());
		}
		if (!StringUtil.isEmpty(entity.getLoanUserName())) {
			querySql.append(" AND olu.user_full_name LIKE ? ");
			sqlPageQuery.addLikeParam(entity.getLoanUserName());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateStart())) {
			querySql.append(" AND oll.payment_time >= ?");
			sqlPageQuery.addParam(entity.getLoanDateStart());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateEnd())) {
			querySql.append(" AND oll.payment_time < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getLoanDateEnd()), 1)));
		}

		if (!StringUtil.isEmpty(entity.getCreateDateStart())) {
			querySql.append(" AND  oll.create_date >= ?");
			sqlPageQuery.addParam(entity.getCreateDateStart());
		}
		if (!StringUtil.isEmpty(entity.getCreateDateEnd())) {
			querySql.append(" AND oll.create_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getCreateDateEnd()), 1)));
		}

		if (!StringUtil.isEmpty(entity.getDateDueStart())) {
			querySql.append(" AND DATE_FORMAT(ADDDATE(oll.payment_time,INTERVAL oll.loan_term  MONTH),'%Y-%m-%d') >= ?");
			sqlPageQuery.addParam(entity.getDateDueStart());
		}
		if (!StringUtil.isEmpty(entity.getDateDueEnd())) {
			querySql.append(" AND DATE_FORMAT(ADDDATE(oll.payment_time,INTERVAL oll.loan_term  MONTH),'%Y-%m-%d') < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getDateDueEnd()), 1)));
		}

		/*
		 * if(!StringUtil.isEmpty(entity.getLtdName())){ querySql.append(
		 * " AND CONCAT(hu.UNITCODE,'-',hu.NAMECNSHORT) LIKE ?");
		 * sqlPageQuery.addLikeParam(entity.getLtdName()); }
		 */
		if (!StringUtil.isEmpty(entity.getLtd())) {
			querySql.append(" AND oll.ltd = ?");
			sqlPageQuery.addParam(entity.getLtd());
		}
		
		if (!StringUtil.isEmpty(entity.getHostProvince())) {
			querySql.append(" AND  opr.province_code  = ? ");
			sqlPageQuery.addParam(entity.getHostProvince());
		}
		if (!StringUtil.isEmpty(entity.getGdStatus())) {
			querySql.append(" AND oll.is_place_on_file = ?");
			sqlPageQuery.addParam(entity.getGdStatus());
		}

		if (!StringUtil.isEmpty(entity.getRepaystatus())) {
			querySql.append(" AND oll.wf_type = ?");
			sqlPageQuery.addParam(entity.getRepaystatus());
		}
		if (!StringUtil.isEmpty(entity.getIdc())) {
			querySql.append(" AND olu.document_no like ?");
			sqlPageQuery.addLikeParam(entity.getIdc());
		}
		if (!StringUtil.isEmpty(entity.getBrandName())) {
			querySql.append(" AND partner.brand_name = ?");
			sqlPageQuery.addParam(entity.getBrandName());
		}
		if (!StringUtil.isEmpty(entity.getPartnerName())) {
			querySql.append(" AND partner.partner_name like ?");
			sqlPageQuery.addLikeParam(entity.getPartnerName());
		}

		querySql.append(" ORDER BY oll.payment_time desc ");
		
		sqlPageQuery.setQuerySql(querySql.toString());
		return loanapplyDao.findDataWithPage(sqlPageQuery);
	}

	/**
	 * 更新主表贷款信息
	 * 
	 * @param loanEntity
	 * @return
	 */
	public int update(O2oBankLoanEntity loanEntity) {
		return loanapplyDao.updateById(loanEntity);
	}

	/**
	 * 
	 * @param 更新主表状态为已结清
	 * @return
	 */
	public int updateLoanStatus(String loanapplyno,String loansettleddate){
		String[] id = loanapplyno.split(",");
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE o2o_loanapply oll SET oll.wf_type = ?,settle_date=? WHERE oll.loanapply_no in (");
		if(id.length>0){
			for(int i =0;i<id.length;i++){
				sql.append("'"+id[i]+"',");
			}
		}else{
			sql.append("'',");
		}
			
		sql.deleteCharAt(sql.length() - 1);
		
		sql.append(" )");
		
		return loanapplyDao.execute(sql.toString(), StatusContexts.O2O_REPAY_END,loansettleddate);
	}
	
	public PageResult<Object[]> findUdateByO2oPage(String loanapplyno){
		SqlPageQuery<Object[]> sqlPageQuery2 = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer querySql2 = new StringBuffer();
		querySql2.append("  SELECT oll.loanapply_no,oll.bill_code,olu.user_full_name,loan.loan_bank,oll.ltd_name,oll.loan_type,opr.province_name,");
		querySql2.append(" CAST(ROUND((CASE WHEN(CASE WHEN (oll.bank_amount ) IS NULL THEN oll.ea_mount ELSE(oll.bank_amount)END) IS NULL THEN ");
		querySql2.append(" oll.loana_mount ELSE oll.ea_mount END),2)as DECIMAL(10,2)) as loan_amount,DATE_FORMAT(loan.loan_date,'%Y-%m-%d') as loan_date,");
		querySql2.append(" DATE_FORMAT(oll.end_bank_date,'%Y-%m-%d')");
		querySql2.append("  from o2o_loanapply oll LEFT JOIN o2o_loan_user olu on oll.loanapply_no = olu.loanapply_no and oll.loan_id = olu.loan_id");
		querySql2.append("  left join  o2o_bank_loan_data loan on oll.bill_code =loan.bill_code ");
		querySql2.append("  LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
		querySql2.append("  where /**DATA_PERMISSION(oll.ltd)**/ oll.loan_flg='Y' and oll.wf_type !='HN'  and olu.sponsor_type is null and olu.spouse_id is null "); 
		querySql2.append(" and oll.loanapply_no in("+loanapplyno+ ")");
		sqlPageQuery2.setQuerySql(querySql2.toString());
		return loanapplyDao.findDataWithPage(sqlPageQuery2);
	}
	
	/**
	 * @Title: findHistoryPayments
	 * @Description: 查询还款历史信息
	 * @author 张庆奇
	 * @param bank_actno
	 * @return
	 * @return PageResult<Object[]>
	 * @throws
	 * @date 2017年5月5日 下午1:40:21
	 */
	public PageResult<Object[]> findHistoryPayments(String bank_actno) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer querySql = new StringBuffer();
		querySql.append("  SELECT termno,trnda,valda,prin,intr,prin_fine,intr_fine,prin_fine_fine from o2o_rphis s where 1=1 ");
		if (!StringUtil.isEmpty(bank_actno)) {
			querySql.append(" AND s.actno= ?");
			sqlPageQuery.addParam(bank_actno);
		}
		querySql.append("order by trnda asc ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return loanapplyDao.findDataWithPage(sqlPageQuery);
	}
  }
